﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BusinessEntities;
using System.Data.Entity;
using System.Data.Objects.SqlClient;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Data;
namespace DAL
{
    public class DalImpl : IDAL
    {
        private EventsDB _database;
        private System.Data.Objects.ObjectSet<BusinessEntities.Model.Event> _events;
        public DalImpl()
        {
            //Database.SetInitializer<EventsDB>(null);
           // Database.SetInitializer(new EventsInitializer());
            _database = new EventsDB();
            _events = (_database as IObjectContextAdapter).ObjectContext.CreateObjectSet<BusinessEntities.Model.Event>();
            _events.MergeOption = System.Data.Objects.MergeOption.OverwriteChanges;
        }
        public IEnumerable<BusinessEntities.Model.City> GetAllCities()
        {
            return _database.Cities;
        }

        public IEnumerable<BusinessEntities.Model.City> GetCitiesByKeyword(string keyword)
        {
            var cities = _database.Cities.ToList();
            return cities.Where(city => city.Name.ToLower().Contains(keyword.ToLower()));
        }

        public IEnumerable<BusinessEntities.Model.City> GetCitiesByStartsWith(string startsWith)
        {
            var cities = _database.Cities.ToList();
            return cities.Where(city => city.Name.StartsWith(startsWith, StringComparison.InvariantCultureIgnoreCase));
        }


        public IEnumerable<BusinessEntities.Model.User> GetAllUsers()
        {
            return _database.Users;
        }

        public BusinessEntities.Model.User GetUserById(int id)
        {
            return _database.Users.Find(id);
        }

        public BusinessEntities.Model.User GetUserByEmail(string email)
        {
            return _database.Users.Where(u => u.Email.Equals(email, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
        }

        public IEnumerable<BusinessEntities.Model.Event> GetUsersEvents(BusinessEntities.Model.User user)
        {
            return _database.Events.Where(e => e.UserID == user.ID);
        }

        public IEnumerable<BusinessEntities.Model.Hall> GetAllHalls()
        {
            return _database.Halls;
        }

        public IEnumerable<BusinessEntities.Model.Invite> GetEventsInvites(BusinessEntities.Model.Event aEvent)
        {
            return _database.Invites.Where(i => i.EventID == aEvent.ID);
        }

        public IEnumerable<BusinessEntities.Model.Guest> GetUsersGuests(BusinessEntities.Model.User user)
        {
            return _database.Guests.Where(g => g.UserID == user.ID);
        }


        public IEnumerable<BusinessEntities.Model.Gift> GetGuestsGifts(BusinessEntities.Model.Guest guest)
        {
            var innerQuery = from i in _database.Invites
                             where i.GuestID == guest.ID
                             select i.GiftID;

            var ret = from gift in _database.Gifts
                      where innerQuery.Contains(gift.ID)
                      select gift;
            return ret;
        }


        public BusinessEntities.Model.User CreateUser(BusinessEntities.Model.User user)
        {
            _database.Users.Add(user);
            _database.SaveChanges();
            return user;
        }

        public BusinessEntities.Model.Event CreateEvent(BusinessEntities.Model.Event aEvent)
        {
            _database.Events.Add(aEvent);
            _database.SaveChanges();
            return aEvent;
        }

        public BusinessEntities.Model.Hall CreateHall(BusinessEntities.Model.Hall hall)
        {
            _database.Halls.Add(hall);
            _database.SaveChanges();
            return hall;
        }

        public BusinessEntities.Model.Invite CreateInvite(BusinessEntities.Model.Invite invite)
        {
            _database.Invites.Add(invite);
            _database.SaveChanges();
            return invite;
        }

        public BusinessEntities.Model.Guest CreateGuest(BusinessEntities.Model.Guest guest)
        {
            _database.Guests.Add(guest);
            _database.SaveChanges();
            return guest;
        }

        public BusinessEntities.Model.Gift CreateGift(BusinessEntities.Model.Gift gift)
        {
            _database.Gifts.Add(gift);
            _database.SaveChanges();
            return gift;
        }

        private void UpdateEntity<T>(T entity) where T : class
        {
            if (entity == null)
            {
                return;
            }

            var entry = _database.Entry<T>(entity);

            if (entry.State == EntityState.Detached)
            {
                var set = _database.Set<T>();
                T attachedEntity = set.Find(entity.GetType().GetProperty("ID").GetValue(entity));  // You need to have access to key

                if (attachedEntity != null)
                {
                    var attachedEntry = _database.Entry(attachedEntity);
                    attachedEntry.CurrentValues.SetValues(entity);
                }
                else
                {
                    entry.State = EntityState.Modified; // This should attach entity
                }
            }
            _database.SaveChanges();
        }

        public BusinessEntities.Model.User UpdateUser(BusinessEntities.Model.User user)
        {
            UpdateEntity(user);
            return user;
        }

        public BusinessEntities.Model.Event UpdateEvent(BusinessEntities.Model.Event aEvent)
        {
          //  UpdateEntity(aEvent);
            //_database.Events.Remove(_database.Events.Find(aEvent.ID));
            //_database.Events.Add(aEvent);
            //_database.SaveChanges();
            var original = _database.Events.Find(aEvent.ID);
            _database.Entry(original).CurrentValues.SetValues(aEvent);
            _database.SaveChanges();
            return aEvent;
        }

        public BusinessEntities.Model.Hall UpdateHall(BusinessEntities.Model.Hall hall)
        {
            UpdateEntity(hall);
            return hall;
        }

        public BusinessEntities.Model.Invite UpdateInvite(BusinessEntities.Model.Invite invite)
        {
            BusinessEntities.Model.Gift gift;
            if (invite.GiftID == null && invite.Gift != null)
            {
                gift = _database.Gifts.Add(invite.Gift);                
                _database.SaveChanges();
                invite.GiftID = gift.ID;
            }
            else if (invite.Gift != null)
            {
                UpdateGift(invite.Gift);
            }
            UpdateEntity(invite);
            return invite;
        }

        public BusinessEntities.Model.Guest UpdateGuest(BusinessEntities.Model.Guest guest)
        {
            UpdateEntity(guest);
            return guest;
        }

        public BusinessEntities.Model.Gift UpdateGift(BusinessEntities.Model.Gift gift)
        {
           // UpdateEntity(gift);
            BusinessEntities.Model.Gift oldGift = _database.Gifts.Find(gift.ID);
            if (oldGift.GetType().Name == gift.GetType().Name)
            {
                UpdateEntity(gift);
            }
            else
            {
                string query = "";
                switch (gift.GetType().Name)
                {
                    case "Cash":
                        BusinessEntities.Model.Cash cash = (BusinessEntities.Model.Cash)gift;
                        query = "UPDATE dbo.Gifts SET Discriminator = 'Cash', Amount = " + cash.Amount + ", Currency = " + ((int)cash.Currency).ToString() + " WHERE ID = " + cash.ID;
                        break;
                    case "Check":
                        BusinessEntities.Model.Check check = (BusinessEntities.Model.Check)gift;
                        query = "UPDATE dbo.Gifts SET Discriminator = 'Check', Amount1 = " + check.Amount + ", Currency1 = " + ((int)check.Currency).ToString() + " WHERE ID = " + check.ID;
                        break;
                    default:
                        query = "UPDATE dbo.Gifts SET Discriminator = '" + gift.GetType().Name + "' WHERE ID = " + gift.ID;
                        break;
                }
                _database.Database.ExecuteSqlCommand(query);
                _database.SaveChanges();
                oldGift = _database.Gifts.Find(gift.ID);                
            }
            _database.Entry(oldGift).CurrentValues.SetValues(gift);
            _database.SaveChanges();
            return gift;
        }


        public void DeleteUser(BusinessEntities.Model.User user)
        {
            BusinessEntities.Model.User userToDelete = _database.Users.Where(u => u.ID == user.ID).FirstOrDefault();

            if (userToDelete != null)
            {
                var arr = _database.Events.Where(e => e.UserID == userToDelete.ID).ToArray();
                for (int i = 0; i < arr.Length; i++)
                {
                    DeleteEvent(arr[i]);
                }
                var gArr = _database.Guests.Where(g => g.UserID == userToDelete.ID).ToArray();
                for (int i = 0; i < gArr.Length; i++)
                {
                    DeleteGuest(gArr[i]);
                }
                _database.Users.Remove(userToDelete);
                _database.SaveChanges();
            }
        }

        public void DeleteEvent(BusinessEntities.Model.Event aEvent)
        {
            foreach (var invite in _database.Invites.Where(i => i.EventID == aEvent.ID))
            {
                if (invite.Gift != null)
                {                    
                    _database.Gifts.Remove(invite.Gift);
                }
                _database.Invites.Remove(invite);
            }
            _database.Events.Remove(_database.Events.Find(aEvent.ID));
            _database.SaveChanges();
        }

        public void DeleteHall(BusinessEntities.Model.Hall hall)
        {
            throw new NotImplementedException();
        }

        public void DeleteInvite(BusinessEntities.Model.Invite invite)
        {
            _database.Invites.Remove(_database.Invites.Find(invite.ID));
            _database.SaveChanges();
        }

        public void DeleteGuest(BusinessEntities.Model.Guest guest)
        {
            var g = _database.Guests.Find(guest.ID);
            if (g != null)
            {
                var inv = _database.Invites.Where(i => i.GuestID == g.ID).ToArray();
                for (int i = 0; i < inv.Length; i++)
                {
                    DeleteInvite(inv[i]);
                }
            }
            _database.Guests.Remove(g);
            _database.SaveChanges();
        }

        public void DeleteGift(BusinessEntities.Model.Gift gift)
        {
            throw new NotImplementedException();
        }


        public BusinessEntities.Model.Event GetEventById(int id)
        {
            
            var ctx = new EventsDB();
            ctx.Events.Load();
            return ctx.Events.Find(id);
            
            //return _events.Where(e => e.ID == id).FirstOrDefault();
           // using (var ctx = new EventsDB())
            //{
             //   return ctx.Events.Find(id);
            //}
            //_database
        }


        public BusinessEntities.Model.City GetCityByName(string name)
        {
            return _database.Cities.Where(c => c.Name == name).FirstOrDefault();
        }


        public BusinessEntities.Model.Guest GetUsersGuestByEmail(string email, int userid)
        {
            return (from g in _database.Guests
                    where g.UserID == userid && g.Email == email
                    select g).FirstOrDefault();
        }


        public BusinessEntities.Model.Guest GetGuestByID(int id)
        {
            return _database.Guests.Find(id);
        }


        public IEnumerable<BusinessEntities.Model.Invite> GetGuestsInvites(BusinessEntities.Model.Guest g)
        {
            return _database.Invites.Where(i => i.GuestID == g.ID);
        }


        public IEnumerable<BusinessEntities.Model.Guest> SearchForGuests(string keyword, int userid)
        {
            keyword = "%" + keyword + "%";
            return (from g in _database.Guests
                    where g.UserID == userid 
                        && (SqlFunctions.PatIndex(keyword,g.FemaleFirstName) > 0 
                        || SqlFunctions.PatIndex(keyword,g.MaleFirstName + g.LastName) > 0
                        || SqlFunctions.PatIndex(keyword,g.Email) > 0)
                    select g);
                
        }


        public IEnumerable<BusinessEntities.Model.Invite> GetAllInvites()
        {
            return _database.Invites;
        }
    }
}
